package com.pkuse.bjousystem.repository.school.enrollment;

import com.pkuse.bjousystem.dto.school.enrollment.EnrollmentDTO;
import com.pkuse.bjousystem.dto.school.graduation.GraduationDTO;
import com.pkuse.bjousystem.repository.school.SchoolTableOptions;
import com.pkuse.bjousystem.repository.school.graduation.GraduationRepository;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.TypedQuery;
import java.util.Collections;
import java.util.List;
import java.util.stream.IntStream;
import java.util.stream.Stream;

public class EnrollmentRepositoryImpl implements EnrollmentRepository {

    private Logger logger = LoggerFactory.getLogger(this.getClass());

    @PersistenceContext
    private EntityManager em;

    @Override
    public Page<EnrollmentDTO> listEnrollment(SchoolTableOptions options, Pageable pageable) {
        String dataSqlPre = "select new com.pkuse.bjousystem.dto.school.enrollment.EnrollmentDTO" +
                "(xsb.nd, xsb.xqdm, xqdmb.xqmc, xsb.xxdm, xxdmb.xxmc, xslbdmb.xslbdm, xslbdmb.xslbmc, xsb.zydm, zydmb.zymc, count(xsb.xh)) " +
                "from Student xsb " +
                "left join XueShengJiBenQingKuang xsjbqkb on xsb.xh=xsjbqkb.xh " +
                "left join XueQiDaiMa xqdmb on xqdmb.xqdm=xsb.xqdm " +
                "left join XueXiaoDaiMa xxdmb on xxdmb.xxdm=xsb.xxdm " +
                "left join BanJiXinXi bjxxb on bjxxb.bdm=xsb.bdm " +
                "left join XueShengLeiBieDaiMa xslbdmb on xslbdmb.xslbdm=bjxxb.xslbdm " +
                "left join ZhuanYeDaiMa zydmb on zydmb.zydm=xsb.zydm " +
                "where xsb.xjztdm='1' and (:xxdm is null or xsb.xxdm = :xxdm) ";
        String dataSqlPost =
                "group by xsb.nd, xsb.xqdm, xqdmb.xqmc, xsb.xxdm, xxdmb.xxmc, xslbdmb.xslbdm, xslbdmb.xslbmc, xsb.zydm, zydmb.zymc " +
                "order by xsb.nd desc, xsb.xqdm desc, xsb.xxdm, xslbdmb.xslbdm, xsb.zydm";

        String countSqlPre = "select count(1) " +
                "from Student xsb " +
                "left join XueShengJiBenQingKuang xsjbqkb on xsb.xh=xsjbqkb.xh " +
                "left join BanJiXinXi bjxxb on bjxxb.bdm=xsb.bdm " +
                "left join XueShengLeiBieDaiMa xslbdmb on xslbdmb.xslbdm=bjxxb.xslbdm " +
                "where xsb.xjztdm='1' and (:xxdm is null or xsb.xxdm = :xxdm) ";
        String countSqlPost = "group by xsb.nd, xsb.xqdm, xsb.xxdm, xslbdmb.xslbdm, xsb.zydm";

        StringBuffer dataSql = new StringBuffer();
        dataSql.append(dataSqlPre);
        StringBuffer countSql = new StringBuffer();
        countSql.append(countSqlPre);

        String[] zydmArray = options.getZhuanYeMingCheng().map(o -> o.toArray(new String[o.size()])).orElse(new String[0]);
        if (options.getZhuanYeMingCheng().isPresent()) {
            Stream.of(dataSql, countSql).forEach(sb -> {
                sb.append("and xsb.zydm in (");
                IntStream.range(0, zydmArray.length).forEachOrdered(i -> sb.append(":zydm" + i + (i < zydmArray.length - 1 ? "," : "")));
                sb.append(") ");
            });
        }

        String[] ndxqArray = options.getNianDuXueQi().map(o -> o.toArray(new String[o.size()])).orElse(new String[0]);
        if (options.getNianDuXueQi().isPresent()) {
            Stream.of(dataSql, countSql).forEach(sb -> {
                sb.append("and (");
                IntStream.range(0, ndxqArray.length).forEach(i -> sb.append("(xsb.nd = :nd" + i + " and xsb.xqdm = :xqdm" + i + ") " + (i < ndxqArray.length - 1 ? "or " : "")));
                sb.append(") ");
            });
        }

        String[] xslbArray = options.getXueShengLeiBie().map(o -> o.toArray(new String[o.size()])).orElse(new String[0]);
        if (options.getXueShengLeiBie().isPresent()) {
            Stream.of(dataSql, countSql).forEach(sb -> {
                sb.append("and xslbdmb.xslbdm in (");
                IntStream.range(0, xslbArray.length).forEachOrdered(i -> sb.append(":xslb" + i + (i < xslbArray.length - 1 ? "," : "")));
                sb.append(") ");
            });
        }

        dataSql.append(dataSqlPost);
        countSql.append(countSqlPost);

        TypedQuery<EnrollmentDTO> dataQuery = em.createQuery(dataSql.toString(), EnrollmentDTO.class);
        Query countQuery = em.createQuery(countSql.toString());

        dataQuery.setParameter("xxdm", options.getSchoolID().getId().orElse(null));
        countQuery.setParameter("xxdm", options.getSchoolID().getId().orElse(null));

        if (options.getZhuanYeMingCheng().isPresent()) {
            Stream.of(dataQuery, countQuery).forEach(query -> IntStream.range(0, zydmArray.length).forEachOrdered(i -> query.setParameter("zydm" + i, zydmArray[i])));
        }
        if (options.getXueShengLeiBie().isPresent()) {
            Stream.of(dataQuery, countQuery).forEach(query -> IntStream.range(0, xslbArray.length).forEachOrdered(i -> query.setParameter("xslb" + i, xslbArray[i])));
        }
        if (options.getNianDuXueQi().isPresent()) {
            Stream.of(dataQuery, countQuery).forEach(query -> IntStream.range(0, ndxqArray.length).forEachOrdered(i -> query.setParameter("nd" + i, ndxqArray[i].split(SchoolTableOptions.DELIMITER)[0])));
            Stream.of(dataQuery, countQuery).forEach(query -> IntStream.range(0, ndxqArray.length).forEachOrdered(i -> query.setParameter("xqdm" + i, ndxqArray[i].split(SchoolTableOptions.DELIMITER)[1])));
        }

        dataQuery.setFirstResult(Long.valueOf(pageable.getOffset()).intValue()); // pageable.getPageSize * pageable.getPageNumber?
        dataQuery.setMaxResults(pageable.getPageSize());

        long total = Integer.valueOf(countQuery.getResultList().size()).longValue();

        List<EnrollmentDTO> content = total > pageable.getOffset() ? dataQuery.getResultList() : Collections.<EnrollmentDTO> emptyList();

        return new PageImpl<>(content, pageable, total);
    }
}
